package crm.implementor;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import crm.daoInterfaces.LeadsDao;
import crm.dbhandelling.Connections;
import crm.dbhandelling.Crude;
import crm.dto.Leads;
import crm.model.FollowUpAction;




public class LeadsImp extends Crude implements LeadsDao
{
   private String className= new Leads().getClass().getName();
	@Override
	public void add(Leads leadDto) throws Exception
	{
		super.insert(leadDto);
		
	}

	@Override
	public void update(Leads leadDto) throws Exception
	{
		super.update(className, "customerId", leadDto.getCustomerId()+"", "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "min_budget", leadDto.getMin_budget()+"", "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "max_budget", leadDto.getMax_budget()+"", "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "infoSourceCode", leadDto.getInfoSourceCode()+"", "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "reference", leadDto.getReference(), "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "enquiryDate", leadDto.getEnquiryDate()+"", "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "productCode", leadDto.getProductCode(), "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "stateId", leadDto.getStateId()+"", "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		super.update(className, "priorityName", leadDto.getPriorityName(), "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
	   
	}

	@Override
	public void delete(Leads leadDto) {
		this.delete(className, "autoenquiryCode", leadDto.getAutoenquiryCode()+"");
		
	}

	

	@Override
	public Leads[] retrieveAll() throws Exception {
	
		Object[] att=super.retreiveAll(className);
		Leads[] s=new Leads[att.length];
		for(int i=0;i<s.length;i++)
				s[i]=(Leads) att[i];
		return s;
		
		
	}

	@Override
	public int getAutoGenratedKey()
	  {
		 int key=super.getAutoGenratedKey(className,"autoenquiryCode") ;
		 return key;
	  }

	@Override
	public Leads retrieve( String conditionField,
			String conditionValue) throws Exception {
		
		Leads l=(Leads) super.retreive(className, conditionField, conditionValue);
		return l;
	}

	@Override
	public Leads[] retrieveArray( String conditionField,
			String conditionValue) throws Exception {
	
		Object[] att=super.retreiveArrayOnCondition(className, conditionField, conditionValue);
		Leads[] s=new Leads[att.length];
		for(int i=0;i<s.length;i++)
				s[i]=(Leads) att[i];
		return s;
	}

public static Leads[] getLeadsByFilter(java.sql.Date From,java.sql.Date To,String attenedBy,String Priority) throws SQLException
	{
		
	String basEsql ="SELECT CUSTOMERINFO.NAME,CUSTOMERINFO.PHONE,LEADS.MIN_BUDGET,LEADS.MAX_BUDGET,INFOSOURCE.INFOSOURCENAME,LEADS.CUSTOMERID,PRODUCT.PRODUCTCODE,LEADS.STATEID,\n"+
				"LEADS.INFOSOURCECODE,PRODUCT.PRODUCTNAME,LEADS.REFERENCE,INQUIRYSTATE.STATENAME,LEADS.ENQUIRYDATE,LEADS.PRIORITYNAME,LEADS.AUTOENQUIRYCODE\n"+
		        "FROM LEADS JOIN CUSTOMERINFO ON LEADS.CUSTOMERID = CUSTOMERINFO.AUTOCUSTOMERID JOIN\n"+ 
		        "PRODUCT ON LEADS.PRODUCTCODE = PRODUCT.PRODUCTCODE\n"+
		        "JOIN INFOSOURCE ON INFOSOURCE.AUTOINFOSOURCECODE = LEADS.INFOSOURCECODE\n"+
		        
		        "JOIN INQUIRYSTATE ON INQUIRYSTATE.AUTOSTATEID = LEADS.STATEID\n";
		        
	String filterDate=" WHERE LEADS.ENQUIRYDATE BETWEEN " +"'"+From+"'" + "AND "+"'"+To+"'" ;
	String filterPriority=" AND LEADS.PRIORITYNAME = "+"'"+Priority+"'";
	
	String sql=basEsql;
	if(From==null && To == null)
		;
	else
	  sql=basEsql+filterDate; 
	
	Priority = Priority.toLowerCase();
	if(Priority.equals("all")==false)
	{
		sql=sql+filterPriority;
	}
	
	         Leads[] leads=null;
		      Statement st=Connections.getMyStatement();
		     
		      try{
		        ResultSet rs= st.executeQuery(sql);
		        
		      int count = 0;
		      while(rs.next())
		    	  count++;
		      leads=new Leads[count];
		      rs= st.executeQuery(sql);
		      int i=0;
		      while(rs.next())
		      {
		    	  
		    	  Leads temp=new Leads();
		    	  temp.setCustomerId(rs.getInt("customerId"));
		    	  temp.setStateId(rs.getInt("STATEID"));
		    	  temp.setProductCode(rs.getString("productCode"));
		    	  temp.setModelname(rs.getString("Name"));
		    	  temp.setModelphone(rs.getDouble("phone"));
		    	  temp.setMin_budget(rs.getDouble("min_budget"));
		    	  temp.setMax_budget(rs.getDouble("max_budget"));
		    	  temp.setModelinfoSourceName(rs.getString("infoSourceName"));
		    	  temp.setInfoSourceCode(rs.getInt("INFOSOURCECODE"));
		    	  temp.setModelproductName(rs.getString("productName"));
		    	  temp.setReference(rs.getString("reference"));
		    	  temp.setModelstateName(rs.getString("stateName"));
		    	  temp.setEnquiryDate(rs.getDate("enquiryDate"));
		    	  temp.setPriorityName(rs.getString("priorityName"));
		    	  System.out.println("PRIORITY NAME IS "+rs.getString("priorityName"));
		    	  temp.setAutoenquiryCode(rs.getInt("autoenquiryCode"));
		    	  
		    	 
		    	  setFollowUpActions(temp);
		    	  
		    	  if(attenedBy.equals("all")==true)
		    	  {
		    		  leads[i]=temp;
		    		  
		    		  i++;
		    	  }
		    	  else
		    	  {
		    	    
		    		
		    	  if(temp.getModelattendedByName().equals(attenedBy))
		    	  {
		    		  leads[i]=temp;
		    		
		    	       i++;
		    	  }
		    	  }
		    	  
		    		  
		    	 
		      }
		      }
		      catch(Exception e)
		      {
		    	 e.printStackTrace();
		    	 System.out.println(sql);
		      }
		
		return leads;
		
	}
	

/*
 * 	
 */
private static void setFollowUpActions(Leads l)
   {
	   
	   FollowUpAction[] followUpAction=null;
	   String baseSql=" SELECT FollowUpAction.autoactionId,FollowUpAction.FOLLOWUPDATE, " +
	   		        "  FollowUpAction.enquiryCode,InquiryState.stateName \n,"+
                    " SalesPerson.salesPersonName,FollowUpAction.comments,FollowUpAction.followUpDate,\n"+
                    " FollowUpAction.followUpTime ,FollowUpAction.ActionName,FollowUpAction.comments  from FollowUpAction join SalesPerson" +
                    " ON FollowUpAction.attendedId = SalesPerson.autosalesPersonId\n"+
                     " JOIN InquiryState on FollowUpAction.stateId = InquiryState.autostateId"
	                 +" WHERE FollowUpAction.enquiryCode = "+l.getAutoenquiryCode()+
	                 " ORDER BY FollowUpAction.followUpTime";
	         
	   
	  // String filterAttended=" AND SALESPERSON.SALESPERSONNAME = "+"'"+attenedBy+"'";
	
	   String sql=baseSql;
	  
	 /*  if(attenedBy.equals("all")==false )
	   {
		   sql=sql+filterAttended+orderBy;
		   
	   }
	   else
	   {
		   sql=sql+orderBy;
	   }*/

	   Connection co=Connections.getMyConnection();
	   
	   try {
		   Statement stat=co.createStatement();
		ResultSet rs= stat.executeQuery(sql);
		int count=0;
		
		while(rs.next())
			count++;
		rs= stat.executeQuery(sql);
		followUpAction = new FollowUpAction[count];
		
		int i=0;
		
		while(rs.next())
		{
			FollowUpAction temp = new FollowUpAction();
			temp.setAutoActionId(rs.getInt("autoactionId"));
			temp.setActionName(rs.getString("actionName"));
			temp.setEnquiryCode(rs.getInt("enquiryCode"));
			temp.setModelSalesPersonName(rs.getString("salesPersonName"));
			temp.setModelStateName(rs.getString("stateName"));
			temp.setComments(rs.getString("comments"));
		    temp.setFollowUpTime(rs.getTimestamp("followUpTime"));
		    temp.setFollowUpDate(rs.getDate("followUpDate"));
		    followUpAction[i]=temp;
		    i++;
		
			
		}
	} catch (SQLException e) {
		
		e.printStackTrace();
		System.out.println(sql);
	}
	l.setModelfoll(followUpAction);
	try
	{
		l.setModelattendedByName(followUpAction[0].getModelSalesPersonName());
	}
	catch(Exception e)
	{
		
		e.printStackTrace();
	}
   }

  
  
  
}

